IF OBJECT_ID('dbo.RptCSCRAgreementsReceivedbyTypeDetail') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptCSCRAgreementsReceivedbyTypeDetail
    IF OBJECT_ID('dbo.RptCSCRAgreementsReceivedbyTypeDetail') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptCSCRAgreementsReceivedbyTypeDetail >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptCSCRAgreementsReceivedbyTypeDetail >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptCSCRAgreementsReceivedbyTypeDetail
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 04/08/2008
--
-- Description: Detail List for Report, "CSCRAgreementsReceivedbyType"
--                  Used for User Testing
--
--
-- Parameters:  
--             @StartDate          Created Date
--             @EndDate            
--             @DomainUserId
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptCSCRAgreementsReceivedbyTypeDetail()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 04/08/08    KC        Created
-- 04/14/08    KC        Using new View for Amendments and Masters
-- 04/16/08    KC        Changed CreateDate to PendingDate
-- 04/17/08    KC        Added table for performance
-- 05/13/08    KC        Added SubAmend Deliverables to Search. Grouped
--                       Other and Misc. Added @IncludeDanaFarber
-- 05/20/08    KC        Fixed DanaFarber flag. not working properly
-- 05/21/08    KC        Converted to use Function (DanaFarber)
-- 05/22/08    KC        DFCPP changed to DFPCC, Added CSCR - Subcontract
--                       to Search Deliverables.
--                       Fixed Counting by Unioning Views.
-- 06/17/08    KC        Added 'Unassigned' for CaseMgr
-- 06/25/08    KC        Changed criteria for 'Clinical Research' 
-- 07/01/08    KC        Change to 'Clinical Research' Logic again.
--
-- =======================================================================
CREATE PROCEDURE dbo.RptCSCRAgreementsReceivedbyTypeDetail
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @CSCRManagerId                     INT=0,
     @SubActivityTypeGroup              VARCHAR(20)='TotalCount',
     @IncludeDanaFarber                 VARCHAR(3) = NULL,
     @DomainUserId                      INT,
     @FolderNumber                      VARCHAR(12)=NULL         -- Not used for Reports, but helpful for investigation.
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     SET ANSI_WARNINGS OFF
     
     -- Declare Local Variables
     DECLARE   @Error         INT,
               @RowCount      INT,
               @Institutions  VARCHAR(50)

     -- ALL checking
     IF @CSCRManagerId IN (NULL,0) SET @CSCRManagerId = NULL

     -- Check DanaFarber
     IF UPPER(@IncludeDanaFarber) != 'YES'
          SET @IncludeDanaFarber = NULL

     -- Set a list of Institutions
     IF @IncludeDanaFarber IS NOT NULL
          SET  @Institutions = 'BWH,MGH,NWH,SRH,DFCI,DFPCC'
     ELSE
          SET  @Institutions = 'BWH,MGH,NWH,SRH'

     -- Need a temp table because last WHERE clause takes 3 mins to complete!
     -- Build table so Proc can fill it.
     DECLARE  @Agreements TABLE (
                    MasterFolderNumber            VARCHAR(12),
                    FolderNumber                  VARCHAR(20),
                    PendingDate                   DATETIME,
                    CSCRManagerId                 INT,
                    CSCRCaseManagerName           VARCHAR(50),
                    Institution                   VARCHAR(50),
                    InfoEdPropNumber              VARCHAR(50),
                    ProposalShortTitle            VARCHAR(64),
                    MasterProposalStatus          VARCHAR(40),
                    OverallStatus                 VARCHAR(50),
                    ProposalStatus                VARCHAR(50),
                    ActivityDescription           VARCHAR(128),
                    SubActivityType               VARCHAR(60),
                    SubActivityGroup              VARCHAR(10),
                    SubActivityGroupSearch        VARCHAR(12),
                    SubmissionType                VARCHAR(30),
                    PrincipalInvestigator         VARCHAR(64)
                    )

     -- Get List of Agreements
     INSERT    @Agreements
               (MasterFolderNumber,     
               FolderNumber,           
               PendingDate,            
               CSCRManagerId,          
               CSCRCaseManagerName,    
               Institution,            
               InfoEdPropNumber,       
               ProposalShortTitle,     
               MasterProposalStatus,   
               OverallStatus,          
               ProposalStatus,         
               ActivityDescription,    
               SubActivityType,        
               SubActivityGroup,       
               SubActivityGroupSearch,
               SubmissionType,
               PrincipalInvestigator)
     SELECT    AI.FolderNumber AS MasterFolderNumber,
               PROPDEL.FolderNumber AS FolderNumber,
               dbo.fnRemoveTime(PROPDEL.PendingDate) AS PendingDate,
               ISNULL(AI.CSCRManagerId,-1),
               ISNULL(AI.CSCRCaseManagerName,'Unassigned'),
               AI.Institution,
               PROPDEL.PropNumber,
               ISNULL(PROPDEL.ShortTitle,AI.ProposalShortTitle),
               AI.MasterProposalStatus,
               AI.OverallStatus,
               AI.ProposalStatus,
               AI.ActivityDescription,
               AI.SubActivityType,
               CASE WHEN AI.SubActivityType LIKE 'Trial%' THEN 'Trial'
                    WHEN AI.SubActivityType LIKE 'Support%' THEN 'Support'
                    WHEN AI.SubActivityType LIKE 'Consulting%' THEN 'Consulting'
                    --WHEN AI.SubActivityType LIKE 'Other%' OR
                    --     (AI.ActivityDescription = 'Other' AND AI.SubActivitytype = 'Clinical Services') THEN 'Other'
                    WHEN AI.SubActivityType LIKE 'CDA%' THEN 'CDA'                   
                    WHEN AI.SubActivityType LIKE 'MTA%' THEN 'MTA'
                    WHEN AI.InstrumentType = 'GIFT' AND AI.SubActivityType = NULL AND AI.SponsorTypeName = 'Industry' THEN 'Gift'
                    ELSE 'MISC' --AI.SubActivityType
               END AS SubActivityGroup,
               CASE WHEN AI.SubActivityType LIKE 'Trial%' THEN 'TrialCount'
                    WHEN AI.SubActivityType LIKE 'Support%' THEN 'SupportCount'
                    WHEN AI.SubActivityType LIKE 'Consulting%' THEN 'ConsultingCount'
                    --WHEN AI.SubActivityType LIKE 'Other%' OR
                    --     (AI.ActivityDescription = 'Other' AND AI.SubActivitytype = 'Clinical Services') THEN 'OtherCount'
                    WHEN AI.SubActivityType LIKE 'CDA%' THEN 'CDACount'                   
                    WHEN AI.SubActivityType LIKE 'MTA%' THEN 'MTACount'
                    WHEN AI.InstrumentType = 'GIFT' AND AI.SubActivityType = NULL AND AI.SponsorTypeName = 'Industry' THEN 'GiftCount'
                    ELSE 'MISCCount' --AI.SubActivityType
               END AS SubActivityGroupSearch,
               PROPDEL.TranType,
               AI.PrincipalInvestigator
     FROM      dbo.AgrAgreement_Info AI INNER JOIN

               -- Master Proposals/Amendments and DELIVERABLES are picked up here...date is checked below
               (-- Deliverables
               SELECT    AI.FolderNumber AS MasterFolderNumber,
                         D.prop_no AS PropNumber,
                         CAST(ReportId AS VARCHAR(20)) AS FolderNumber,
                         ReportName AS ShortTitle,
                         REPORTCAT AS TranType,
                         MIN(ProcessedDate) AS PendingDate
               FROM      dbo.vwInfoEdCSCRDeliverables D
                         INNER JOIN dbo.AgrAgreement_Info AI ON D.prop_no = AI.InfoEdPropNumber
               WHERE     REPORTCAT_CODE IN ('REPORTCAT_36','REPORTCAT_34') AND     -- 'CSCR - Sub-Amendment' OR 'CSCR - Subcontract'
                         REPORTSTAT_CODE = 'REPORTSTAT_61'       -- 'Pending'
               GROUP BY  AI.FolderNumber,
                         D.prop_no,
                         ReportId,
                         ReportName,
                         REPORTCAT
               UNION
               -- Master and Amendments
               SELECT    MA.MasterFolderNumber,
                         MA.prop_no,
                         MA.inst_no,
                         NULL,          -- Get Short Title from Master
                         MA.TranType,
                         PendingDate
               FROM      dbo.vwInfoEdMasterAmendments MA ) PROPDEL ON AI.FolderNumber = PROPDEL.MasterFolderNumber

     WHERE     -- This is the Where clause from the Parent Report...
               (dbo.fnRemoveTime(PROPDEL.PendingDate) BETWEEN  @StartDate AND @EndDate) AND

               -- Certain Criteria to count: Definition of 'Clinical Research'
               -- Defn #1
               ((RTRIM(AI.ActivityDescription) = 'Clinical Research' AND 
               RTRIM(AI.SponsorTypeName) = 'Industry' AND
               RTRIM(AI.InstrumentType) = 'Contract' AND
               RIGHT(RTRIM(AI.SubActivityType ),3) = '(C)') 
               OR
               -- Defn #2
               (RTRIM(AI.ActivityDescription) = 'Clinical Research' AND 
               RTRIM(AI.SponsorTypeName) = 'Industry' AND
               RTRIM(AI.InstrumentType) = 'Gift' AND
               RTRIM(AI.SubActivityType) IN ('',NULL))    )       AND

               (AI.Institution IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Institutions))) AND
               (@FolderNumber IS NULL OR AI.FolderNumber = @FolderNumber)
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return ResultSet
     SELECT    MasterFolderNumber,     
               FolderNumber,           
               PendingDate,            
               CSCRManagerId,          
               CSCRCaseManagerName,    
               Institution,            
               InfoEdPropNumber,       
               ProposalShortTitle,     
               MasterProposalStatus,   
               OverallStatus,          
               ProposalStatus,         
               ActivityDescription,    
               SubActivityType,        
               SubActivityGroup,
               SubmissionType,
               PrincipalInvestigator
     FROM      @Agreements A
     -- Which Column?
     -- These are additional Criteria to provide drilldown to this report
     -- (based on which number the user clicked on in the ParentReport
     WHERE     (@SubActivityTypeGroup = 'TotalCount' OR
               @SubActivityTypeGroup = A.SubActivityGroupSearch) AND
               (@CSCRManagerId IS NULL OR A.CSCRManagerId = @CSCRManagerId) 
     ORDER BY A.PendingDate                         
  
  
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1
     
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptCSCRAgreementsReceivedbyTypeDetail') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRAgreementsReceivedbyTypeDetail >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptCSCRAgreementsReceivedbyTypeDetail >>>'
go

GRANT EXECUTE ON dbo.RptCSCRAgreementsReceivedbyTypeDetail TO InsightReport,Insight
go

